/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bdd;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import weka.core.Instances;
import weka.core.converters.DatabaseSaver;
import weka.experiment.InstanceQuery;

/**
 *
 * @author Raphaël
 */
public class Connexion {

    String jdbcDriver = "com.mysql.jdbc.Driver";
//    String url = "jdbc:mysql://mysql.montpellier.epsi.fr:5206/WEKA";
    String url = "jdbc:mysql://mysql.montpellier.epsi.fr:3306/WEKA";
    String user = "jw_mboungou";
    String passwd = "epsi172CTM";
    String pathFileAnimaux = "./Animaux.arff";
    int compteurTest = 0;
    private static Connexion instance = null;
    private Connection connectionDriver;

    private Connexion() throws Exception {
        super();
        setConnexion();
    }

    public static Connexion getInstance() throws Exception {
        if (instance == null) {
            instance = new Connexion();
        }
        return instance;
    }

    private void setConnexion() {
        try {
            Class.forName(jdbcDriver);
            System.out.println("DRIVER OK ! ");
            tracerTest();
            connectionDriver = DriverManager.getConnection(url, user, passwd);
            System.out.println("Connection effective !");

        } catch (Exception e) {
            System.out.println(e);
        }

    }

    public Connection getConnectionDriver() {
        return connectionDriver;
    }

    public Instances creerInstances(String requete) throws Exception {

        InstanceQuery query = new InstanceQuery();
        query.setUsername(user);
        query.setPassword(passwd);
        query.setQuery(requete);
//         if your data is sparse, then you can say so too
//         query.setSparseData(true);
        Instances dataSet = query.retrieveInstances();
        return dataSet;
    }

    public void creerArff(String query) throws IOException, Exception {
        Instances dataSet = creerInstances(query);
        BufferedWriter writer = new BufferedWriter(new FileWriter(pathFileAnimaux));
        writer.write(dataSet.toString());
        writer.flush();
        writer.close();
    }

    public void creerArffAnimaux() {
        try {
            creerArff("SELECT * FROM Animaux");
        } catch (IOException ex) {
            Logger.getLogger(Connexion.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(Connexion.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ResultSet insererAnimal(ArrayList<String> listeValeur) throws SQLException {
        int numeroListe = 0;
        Statement state = connectionDriver.createStatement();
        // name_animal,hair,feathers,eggs,milk,airborne,aquatic,predator,toothed,backbone,breathes,venomous,fins,legs,tail,domestic,catsize,type
        ResultSet result = state.executeQuery("REPLACE INTO Animaux VALUE ('" + listeValeur.get(numeroListe++)
                + "','" + listeValeur.get(numeroListe++) + "','" + listeValeur.get(numeroListe++) + "','" + listeValeur.get(numeroListe++)
                + "','" + listeValeur.get(numeroListe++) + "','" + listeValeur.get(numeroListe++) + "','" + listeValeur.get(numeroListe++)
                + "','" + listeValeur.get(numeroListe++) + "','" + listeValeur.get(numeroListe++) + "','" + listeValeur.get(numeroListe++)
                + "','" + listeValeur.get(numeroListe++) + "','" + listeValeur.get(numeroListe++) + "','" + listeValeur.get(numeroListe++)
                + "','" + listeValeur.get(numeroListe++) + "','" + listeValeur.get(numeroListe++) + "','" + listeValeur.get(numeroListe++)
                + "','" + listeValeur.get(numeroListe++) + "','" + listeValeur.get(numeroListe++) + "');");
        state.close();
        creerArffAnimaux();
        return result;
    }

    public int supprimerAnimal(String animal) throws SQLException {
        Statement state = connectionDriver.createStatement();
        // name_animal,hair,feathers,eggs,milk,airborne,aquatic,predator,toothed,backbone,breathes,venomous,fins,legs,tail,domestic,catsize,type
        int result = state.executeUpdate("DELETE FROM Animaux WHERE name_animal = '" + animal + "';");
        state.close();
        creerArffAnimaux();
        return result;
    }

    public ArrayList<String> getListTypeAnimaux() throws SQLException {
        Statement state = connectionDriver.createStatement();
        // name_animal,hair,feathers,eggs,milk,airborne,aquatic,predator,toothed,backbone,breathes,venomous,fins,legs,tail,domestic,catsize,type
        ResultSet result = state.executeQuery("SELECT nom_type FROM Type;");
        ArrayList<String> listTypeAnimaux = new ArrayList();
        while (result.next()) {
            listTypeAnimaux.add(result.getString(1));
        }
        state.close();
        return listTypeAnimaux;
    }

    public void Arff2Database(String pathFile, String tableNom) throws Exception {
        Instances data = new Instances(new BufferedReader(new FileReader(pathFile)));
        data.setClassIndex(data.numAttributes() - 1);

        DatabaseSaver save = new DatabaseSaver();
        save.setUrl(url);
        save.setUser(user);
        save.setPassword(passwd);
        save.setInstances(data);
        save.setRelationForTableName(false);
        save.setTableName(tableNom);
        save.connectToDatabase();
        save.writeBatch();
    }

    private void tracerTest() {
        System.out.println("Test numéro : " + ++compteurTest);
    }
}
